﻿

--获得本单位转正人员数据
CREATE proc [dbo].[proc_Employee_ProbationPassed_GetList]
(
	@startIndex nvarchar(20),
	@endIndex nvarchar(20),
	@docount bit,
	@username nvarchar(50),
	@ename nvarchar(50),
	@apply nvarchar(4),
	@audit nvarchar(4),
	@cid nvarchar(400)
)
as
declare @sql nvarchar (4000)
DECLARE @te NVARCHAR(400)
SET @te=''
IF(@cid<>'0')
SET @te=@te+'e.companyid in ('+@cid+')'
ELSE
SET @te=@te+'e.id<>0 and e.companyid is not null '
IF(@apply='0')
SET @te=@te+' and ee.proposer is null '
IF(@apply='1')
SET @te=@te+' and ee.proposer is not null '
BEGIN				
	set @sql =
		+ ' declare @indextable table(id int identity(1,1),nid int)'
		+ ' insert into @indextable(nid)'
		+ ' select EE.id from employee_ProbationPassed EE inner join Employee E on EE.EId=E.Id'
	    + ' where '
		+  @te
		+ '	and e.ename like ''%'+@ename+'%'''
		--+ ' and (case (isnull(ee.proposer,0)) when 0 then 0 else 1 end) like ''%'+@apply+'%'''
		+ '	and (case when ee.audit=1 then 1'
		+ '		 when ee.audit=0 and ee.auditperson is null then 0'
		+ '		 when ee.audit=0 and ee.auditperson is not null then 2 end) like ''%'+@audit+'%'''	
		+ '	and  e.state <> 5'
		+ ' order by ee.audit,E.RuzhiDate Desc'
		
		+ ' select' 
		+ ' EE.Id,'
		+ ' (select ENumber from Employee E where EE.EId=E.Id) as ENumber,'
		+ ' (select ETempNumber from Employee E where EE.EId=E.Id) as ETempNumber,'
		+ ' EE.EId,'
		+ ' (select EName from Employee E where EE.EId=E.Id) as EName,'
		+ ' (select Sex from Employee E where EE.EId=E.Id) as Sex,'
		+ ' (select CompanyName from Company where id=(select companyid from employee E where EE.EId=E.Id)) as CompanyName,'
		+ ' (select DName from Department where id=(select departmentid from employee E where EE.EId=E.Id)) as DName,'
		+ ' (select Birthday from Employee E where EE.EId=E.Id) as Birthday,'
		+ ' (select Title from Employee_Education where id=(select Education from employee E where EE.EId=E.Id)) as Education,'
		+ ' (select PostName from Post P where id=(select PostId from employee E where EE.EId=E.Id)) as PostName,'
		+ ' (select RuzhiDate from Employee E where EE.EId=E.Id) as RuzhiDate,'
		+ ' (SELECT COUNT(0) FROM @indextable ) AS coun,'
		+ ' EE.Audit,'
		+ ' EE.AuditPerson,'
		+ ' EE.AuditTime,'
		+ ' EE.proposer,'
		+ ' CONVERT(varchar(12), ApplionTime, 23 ) as ApplionTime'
		+ ' from employee_ProbationPassed EE'
		+ '	inner join @indextable t on '
		+ ' EE.id=t.nid '
		+ ' where t.id between '+ @startIndex +' and '+ @endIndex 
		+ ' Order By t.id'	
	END

EXEC(@sql)


----获得本单位转正人员数据
--ALTER proc [dbo].[proc_Employee_ProbationPassed_GetList]
--(
--	@startIndex int,
--	@endIndex int,
--	@docount bit,
--	@username nvarchar(50),
--	@ename nvarchar(50),
--	@apply nvarchar(4),
--	@audit nvarchar(4),
--	@cid INT
--)
--as

----公司Id
--declare @companyid nvarchar(4)
----set @companyid = (select CompanyId from aspnet_Users where username=@username)
--IF(@cid=0)
--SET @companyid = '%'
--ELSE
--	SET @companyid=@cid
--set nocount on
--	if(@docount=1)
--		select *,'EName' as EName,'Sex' as Sex,'CompanyName' as CompanyName,'DName'as DName,'Birthday'as Birthday,'Education' as Education,'PostName' as PostName,'RuzhiDate'as RuzhiDate from employee_ProbationPassed EE inner join Employee E on EE.EId=E.Id 
--		where 
--			    e.companyid LIKE @companyid 
--			and e.ename like '%'+@ename+'%' 
--			and (case (isnull(ee.proposer,'0')) when '0' then '0' else '1' end) like '%'+@apply+'%'
--			and (case when ee.audit=1 then '1' 
--				 when ee.audit=0 and ee.auditperson is null then '0'
--				 when ee.audit=0 and ee.auditperson is not null then '2' end) like '%'+@audit+'%'
--			and  e.state <> 5
--	else
--	begin
--	declare @indextable table(id int identity(1,1),nid int)
----	set rowcount @endIndex
--	insert into @indextable(nid) select EE.id from employee_ProbationPassed EE inner join Employee E on EE.EId=E.Id 
--		where 
--			e.companyid LIKE @companyid 
--			and e.ename like '%'+@ename+'%' 
--			and (case (isnull(ee.proposer,'0')) when '0' then '0' else '1' end) like '%'+@apply+'%'
--			and (case when ee.audit=1 then '1' 
--				 when ee.audit=0 and ee.auditperson is null then '0'
--				 when ee.audit=0 and ee.auditperson is not null then '2' end) like '%'+@audit+'%'
--			and  e.state <> 5
--			order by E.RuzhiDate asc
--	select 
--		EE.Id,
--		(select ENumber from Employee E where EE.EId=E.Id) as ENumber,
--		(select ETempNumber from Employee E where EE.EId=E.Id) as ETempNumber,
--		EE.EId,
--		(select EName from Employee E where EE.EId=E.Id) as EName,
--		(select Sex from Employee E where EE.EId=E.Id) as Sex,
--		(select CompanyName from Company where id=(select companyid from employee E where EE.EId=E.Id)) as CompanyName,
--		(select DName from Department where id=(select departmentid from employee E where EE.EId=E.Id)) as DName,
--		(select Birthday from Employee E where EE.EId=E.Id) as Birthday,
--		(select Title from Employee_Education where id=(select Education from employee E where EE.EId=E.Id)) as Education,
--		(select PostName from Post P where id=(select PostId from employee E where EE.EId=E.Id)) as PostName,
--		(select RuzhiDate from Employee E where EE.EId=E.Id) as RuzhiDate,
--		(SELECT COUNT(0) FROM @indextable ) AS coun,
--		EE.Audit,
--		EE. AuditPerson,
--		EE.AuditTime,
--		EE.proposer,
--		CONVERT(varchar(12), ApplionTime, 23 ) as ApplionTime		

--	from employee_ProbationPassed EE
--	inner join @indextable t on 
--	EE.id=t.nid
--	where 
--		t.id between @startIndex and @endIndex 
--	order by t.id
--	end
--set nocount off
RETURN



